﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BLL;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DAL
{
    public class UserImplementation : IUserRepository
    {
        //public  bool Add(User User)
        //{

        //    string sql = "insert into [User] (FirstName,LastName,DepartmentId,RoleId) values (@FirstName,@LastName,@DepartmentId,@RoleId)";

        //    return DBHelper.ExeCUD(sql, new SqlParameter("@FirstName", User.FirstName), new SqlParameter("@LastName", User.LastName), new SqlParameter("@DepartmentId", User.departmentId.DepartmentId), new SqlParameter("@RoleId", User.roleId.RoleId)) > 0 ? true : false;

        //    //string sql = string.Format("insert into [User](FirstName, LastName, DepartmentId, RoleId) values('{0}','{1}', {2}, {3})", User.FirstName, User.LastName, User.DepartmentId, User.RoleId);
        //    //return DBHelper.ExeCUD(sql) > 0 ? true : false;
        //}
        public int Add(BLL.User User)
        {
            DAL.User user = new DAL.User();
            user = UserTranslator.TranslateBLLToDAL(User);
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                return Add(db, user);
            }
        }

        private int Add(TrainingWorkshopEntities2 db, User User)
        {
            BLL.User user = new BLL.User();
            user = Translator.TranslateToUser(User);
            return db.User_Add(User.FirstName, User.LastName, User.DepartmentId, User.RoleId).ElementAt(0).Value;
        }
        //public  bool Delete(int UserId)
        // {

        //     string sql = "Delete from [User] where UserId=@UserId";
        //     return DBHelper.ExeCUD(sql, new SqlParameter("@UserId", UserId)) > 0 ? true : false;
        //}
        public void Delete(int UserId)
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                Delete(db, UserId);
            }
        }

        private void Delete(TrainingWorkshopEntities2 db, int UserId)
        {
            db.User_Delete(UserId);
        }

        // public  bool Modify(User User)
        //{
        //     string sql = "Update [User] set FirstName=@FirstName,LastName=@LastName,DepartmentId=@DepartmentId,RoleId=@RoleId where UserId=@UserId";
        //     return DBHelper.ExeCUD(sql, new SqlParameter("@UserId", User.UserId), new SqlParameter("@FirstName", User.FirstName), new SqlParameter("@LastName", User.LastName), new SqlParameter("@DepartmentId", User.departmentId.DepartmentId), new SqlParameter("@RoleId", User.roleId.RoleId)) > 0 ? true : false;
        //}
        public void Modify(BLL.User User)
        {
            DAL.User user = new DAL.User();
            user = UserTranslator.TranslateBLLToDAL(User);
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                Modify(db, user);
            }
        }
       
        private void Modify(TrainingWorkshopEntities2 db, User User)
        {
            BLL.User user = new BLL.User();
            user = Translator.TranslateToUser(User);
            db.User_Modify(User.UserId, User.FirstName, User.LastName, User.DepartmentId, User.RoleId);
        }
        //public  List<User> GetAllUser()
        //{
        //    string sql = "select UserId, FirstName, LastName, d.DepartmentName,d.DepartmentId, r.RoleName,r.RoleId from [User] u join Department d on u.DepartmentId = d.DepartmentId join Role r on u.RoleId = r.RoleId";
        //    DataTable dt = DBHelper.ExeAll(sql);
        //    List<User> userList = new List<User>();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        User User = new User();
        //        User.UserId = Convert.ToInt32(dr["UserId"]);
        //        User.FirstName = (string)dr["FirstName"];
        //        User.LastName = (string)dr["LastName"];
        //        User.departmentId.DepartmentId = Convert.ToInt32(dr["DepartmentId"]);
        //        User.departmentId.DepartmentName = dr["DepartmentName"].ToString();
        //        User.roleId.RoleId = Convert.ToInt32(dr["RoleId"]);
        //        User.roleId.RoleName = dr["RoleName"].ToString();
        //        userList.Add(User);
        //    }
        //    return userList;
        //}
        public List<BLL.User> GetAllUser()
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {

                return GetAllUser(db);
            }
        }
        
        private List<BLL.User> GetAllUser(TrainingWorkshopEntities2 db)
        {
            List<BLL.User> listBLL = new List<BLL.User>();
            List<DAL.User> listDAL = db.User_GetAllUser().ToList();
            foreach (DAL.User userDAL in listDAL)
            {
                BLL.User userBLL = Translator.TranslateToAllUser(userDAL);
                listBLL.Add(userBLL);
            }
            return listBLL;
        }

        //public User GetOneUser(int userId)
        //{
        //    string sql = "select FirstName,LastName,d.DepartmentId,d.DepartmentName,r.RoleId,r.RoleName from [User] u join Department d on u.DepartmentId=d.DepartmentId join Role r on u.RoleId=r.RoleId where u.UserId=@userId";
        //    DataTable dt = DBHelper.ExeAll(sql, new SqlParameter("@userId", userId));
        //    User User = new User();
        //    if (dt.Rows.Count > 0)
        //    {
        //        DataRow dr = dt.Rows[0];
        //        User.UserId = userId;
        //        User.FirstName = dr["FirstName"].ToString();
        //        User.LastName = dr["LastName"].ToString();
        //        User.departmentId.DepartmentId = Convert.ToInt32(dr["DepartmentId"]);
        //        User.departmentId.DepartmentName = dr["DepartmentName"].ToString();
        //        User.roleId.RoleId = Convert.ToInt32(dr["RoleId"]);
        //        User.roleId.RoleName = dr["RoleName"].ToString();
        //    }
        //    return User;
        //}
        public BLL.User GetOneUser(int UserId)
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                return GetOneUser(db, UserId);
            }
        }
        
        private BLL.User GetOneUser(TrainingWorkshopEntities2 db, int UserId)
        {
            return Translator.TranslateToAllUser(db.User_GetOneUser(UserId).ElementAtOrDefault(0));
        }

        //public  List<User> SearchUser(int departmentid, string username)
        //{

        //    string sql = "select UserId,FirstName,LastName,d.DepartmentName,d.DepartmentId,r.RoleName,r.RoleId from [User] u join Department d on u.DepartmentId=d.DepartmentId join Role r on u.RoleId=r.RoleId where u.DepartmentId=@departmentid and (u.FirstName like @username or u.LastName like @username)";
        //    DataTable dt = DBHelper.ExeAll(sql, new SqlParameter("@departmentId", departmentid), new SqlParameter("@username", username + "%"));
        //    List<User> userList = new List<User>();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        User User = new User();
        //        User.UserId = Convert.ToInt32(dr["UserId"]);
        //        User.FirstName = (string)dr["FirstName"];
        //        User.LastName = (string)dr["LastName"];
        //        User.departmentId.DepartmentId = Convert.ToInt32(dr["DepartmentId"]);
        //        User.departmentId.DepartmentName = dr["DepartmentName"].ToString();
        //        User.roleId.RoleId = Convert.ToInt32(dr["RoleId"]);
        //        User.roleId.RoleName = dr["RoleName"].ToString();
        //        userList.Add(User);
        //    }
        //    return userList;
        //}
        public List<BLL.User> SearchUser(int departmentid, string username)
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                return SearchUser(db, departmentid, username + "%");
            }
        }
        public BLL.User Tosearchuser(int department, string username)
        {
            BLL.User User = new BLL.User();
            User.FirstName = username;
            User.LastName = username;
            User.departmentId.DepartmentId = department;
            return User;
        }
        private List<BLL.User> SearchUser(TrainingWorkshopEntities2 db, int departmentid, string username)
        {
            List<BLL.User> listBLL = new List<BLL.User>();
            List<DAL.User> listDAL = db.User_SearchUser(departmentid, username + "%").ToList();
            foreach (DAL.User userDAL in listDAL)
            {
                BLL.User userBLL = Translator.TranslateToAllUser(userDAL);
                listBLL.Add(userBLL);
            }
            return listBLL;
        }
        //public  List<Department> GetAllDepartment()
        //{
        //    string sql = "select DepartmentName,DepartmentId from [TrainingWorkshop].[dbo].[Department]";
        //    DataTable dt = DBHelper.ExeAll(sql);
        //    List<Department> departmentList = new List<Department>();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        Department department = new Department();
        //        department.DepartmentName = (string)dr["DepartmentName"];
        //        department.DepartmentId = Convert.ToInt32(dr["DepartmentId"]);
        //        departmentList.Add(department);
        //    }
        //    return departmentList;
        //}
        public List<BLL.Department> GetAllDepartment()
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                return GetAllDepartment(db);
            }
        }
       
        private List<BLL.Department> GetAllDepartment(TrainingWorkshopEntities2 db)
        {
            List<BLL.Department> listBLL = new List<BLL.Department>();
            List<DAL.Department> listDAL = db.Department_GetAllDepartment().ToList();
            foreach (DAL.Department deptDAL in listDAL)
            {
                BLL.Department deptBLL = Translator.TranslateToAllDepartment(deptDAL);
                listBLL.Add(deptBLL);
            }
            return listBLL;
        }
        //public  List<Role> GetAllRole()
        //{
        //    string sql = "select RoleName,RoleId,s.StatusName,s.StatusId from Role r join StatusText s on r.StatusId=s.StatusId where s.StatusId=1";
        //    DataTable dt = DBHelper.ExeAll(sql);
        //    List<Role> roleList = new List<Role>();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        Role role = new Role();
        //        role.RoleName = (string)dr["RoleName"];
        //        role.RoleId = Convert.ToInt32(dr["RoleId"]);
        //        //role.statusId.StatusId = Convert.ToInt32(dr["StatusId"]);
        //        //role.statusId.StatusName = dr["StatusName"].ToString();
        //        roleList.Add(role); 
        //    }
        //    return roleList;
        //}
        public List<BLL.Role> GetAllRole()
        {
            using (TrainingWorkshopEntities2 db = new TrainingWorkshopEntities2())
            {
                return GetAllRole(db);
            }
        }
        
        private List<BLL.Role> GetAllRole(TrainingWorkshopEntities2 db)
        {
            List<BLL.Role> listBLL = new List<BLL.Role>();
            List<DAL.Role> listDAL = db.Role_GetAllRole().ToList();
            foreach (DAL.Role rDAL in listDAL)
            {
                BLL.Role rBLL = Translator.TranslateToAllRole(rDAL);
                listBLL.Add(rBLL);
            }
            return listBLL;
        }
    }
}



